﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DBAccess
/// </summary>
public class DBAccess
{
    private static DataSet ds;
    private static string sqlString;
    private static SqlConnection con = new SqlConnection();
    public static int TaoTaiKhoan(string TenDangNhap ,string MatKhau ,string Ten ,int NoiSinhID ,string  Mobile, string Phone, string Email, string Website,string  CMND,DateTime NgaySinh,int  QuyenHan,string  Avarta,int  NganHangID ,string  SoTK )
    {
        int result = 0;
        con = GetCenection.GetConection();
        sqlString = "TaoTaiKhoan";
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
           con.Open();
           command.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar,50).Value = TenDangNhap ;
	       command.Parameters.Add("@MatKhau", SqlDbType.NVarChar,100).Value = MatKhau ;
	       command.Parameters.Add("@Ten", SqlDbType.NVarChar,150).Value =  Ten;
	       command.Parameters.Add("@NoiSinhID", SqlDbType.Int).Value = NoiSinhID ;
	       command.Parameters.Add("@Mobile", SqlDbType.NVarChar,100).Value = Mobile ;
	       command.Parameters.Add("@Phone", SqlDbType.NVarChar,100).Value = Phone ;
	       command.Parameters.Add("@Email", SqlDbType.NVarChar,100).Value = Email ;
	       command.Parameters.Add("@Website", SqlDbType.NVarChar,100).Value = Website  ;
	       command.Parameters.Add("@CMND", SqlDbType.NVarChar,20).Value =CMND  ;
	       command.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value =NgaySinh ;
	       command.Parameters.Add("@QuyenHan", SqlDbType.Int).Value = 1;// đã kích hoặt
	       command.Parameters.Add("@Avarta", SqlDbType.NVarChar,250).Value =  Avarta;
	       command.Parameters.Add("@NganHangID", SqlDbType.Int).Value = NganHangID;
	       command.Parameters.Add("@SoTK", SqlDbType.NVarChar,20).Value =  SoTK;
           command.Parameters.Add("@DangNhapTruoc", SqlDbType.DateTime).Value =DateTime.Now ;
           result = (int)command.ExecuteNonQuery();
           con.Close();
        }
        catch
        {
            result = 0;
            con.Close();
        }
        return result;
    }  
    public static int ktTaiKhoan(string idAcount, string pass)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "";
        if (pass.Equals(""))
            sqlQuery = "select QuyenHan from TaiKhoan where TaiKhoan.TenDangNhap = '" + idAcount + "'";
        else
            sqlQuery = "select QuyenHan from TaiKhoan where TaiKhoan.TenDangNhap = '" + idAcount + "' AND TaiKhoan.MatKhau ='" + pass + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            try
            {
                result = (int)command.ExecuteScalar();
            }
            catch(NullReferenceException) {
                result = -1;
            }
        }
        catch (SqlException)
        {
            return 0;
        }
        return result;
    }  
    public static TaiKhoan layTK(string tenDangNhap, string matKhau)
    {
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select * from TaiKhoan where TaiKhoan.TenDangNhap = '" + tenDangNhap + "' AND TaiKhoan.MatKhau ='" + matKhau + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "Acount");
            con.Close();
            TaiKhoan acount = new TaiKhoan();
            acount.Avarta = ds.Tables[0].Rows[0]["Avarta"].ToString();
            acount.Ten = ds.Tables[0].Rows[0]["Ten"].ToString();
            acount.TenDangNhap = ds.Tables[0].Rows[0]["TenDangNhap"].ToString();
            acount.QuyenHan = ds.Tables[0].Rows[0]["QuyenHan"].ToString();
            acount.NoiSinhID = (int)ds.Tables[0].Rows[0]["NoiSinhID"];
            acount.Email = ds.Tables[0].Rows[0]["Email"].ToString();
            acount.Website = ds.Tables[0].Rows[0]["Website"].ToString();
            acount.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
            acount.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
            acount.CMND = ds.Tables[0].Rows[0]["CMND"].ToString();
            acount.NgaySinh = (DateTime)ds.Tables[0].Rows[0]["NgaySinh"];
            acount.NganHangID = (int)ds.Tables[0].Rows[0]["NganHangID"];
            acount.SoTK = ds.Tables[0].Rows[0]["SoTK"].ToString();
            acount.DangNhapTruoc = (DateTime)ds.Tables[0].Rows[0]["DangNhapTruoc"];
            return acount;
        }
        catch
        {
            con.Close();
            return null;
        }
    }

    //public static int UpdateAcount(Acount acount)
    //{

    //    con = GetCenection.GetConection();
    //    string sqlQuery = "UPDATE Acount SET " +
    //                      "Pass ='" + acount.Pass + "'" +
    //                      ",Name ='" + acount.Name + "'" +
    //                      ",Email ='" + acount.Email + "'" +
    //                      ",Address ='" + acount.Address + "'" +
    //                      ",BankName ='" + acount.BankName + "'" +
    //                      ",IDBank ='" + acount.BankID + "'" +
    //                      ",Point ='" + acount.Point + "'" +
    //                      ",BirthDay ='" + acount.BirthDay.ToShortDateString() + "'" +
    //                          ",Avarta ='" + acount.Avarta + "'" +
    //                          ",CMND ='" + acount.CMND + "'" +
    //                          ",Mobile ='" + acount.Mobile + "'" +
    //                        "WHERE Acount.IDAcount ='" + acount.IDAcount + "'";
    //    SqlCommand command = new SqlCommand(sqlQuery, con);
    //    command.CommandType = CommandType.Text;
    //    con.Open();
    //    try
    //    {
    //        command.ExecuteNonQuery();
    //        con.Close();
    //    }
    //    catch
    //    {
    //        con.Close();
    //    }
    //}
  
    internal static int getGianHang(string acount)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "select count(TenDangNhap) from GianHang where GianHang.TenDangNhap = '" + acount + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = (int)command.ExecuteScalar();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }

    internal static int updateAcount(TaiKhoan acount)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "update TaiKhoan set " +
            "Ten = '" + acount.Ten +"'"+
            ",NoiSinhID ='" + acount.NoiSinhID + "'" +
            ",Mobile ='" + acount.Mobile + "'" +
            ",Phone ='" + acount.Phone + "'" +
            ",Email ='" + acount.Email + "'" +
            ",Website ='" + acount.Website + "'" +
            ",CMND ='" + acount.CMND + "'" +
            ",NgaySinh ='" + acount.NgaySinh + "'" +
            ",QuyenHan ='" + acount.QuyenHan + "'" +
            ",Avarta ='" + acount.Avarta + "'" +
            ",NganHangID ='" + acount.NganHangID + "'" +
            ",SoTK ='" + acount.SoTK + "'" +
            " Where TaiKhoan.TenDangNhap='" + acount.TenDangNhap+"'";
      
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = command.ExecuteNonQuery();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }
    public static int GuiThu(Thu thu)
    {
        con = GetCenection.GetConection();
        sqlString = "GuiThu";
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            con.Open();
            command.Parameters.Add("@NgayGui", SqlDbType.DateTime).Value = thu.NgayGui;
            command.Parameters.Add("@NguoiGui", SqlDbType.NVarChar, 50).Value = thu.NguoiGui;
            command.Parameters.Add("@NguoiNhan", SqlDbType.NVarChar, 50).Value = thu.NguoiNhan;
            command.Parameters.Add("@TieuDe", SqlDbType.NVarChar, 150).Value = thu.TieuDe;
            command.Parameters.Add("@NoiDung", SqlDbType.NVarChar, 500).Value = thu.NoiDung;
            command.Parameters.Add("@OpenID", SqlDbType.Bit).Value = thu.OpenID;
            command.Parameters.Add("@Email", SqlDbType.NVarChar, 150).Value = thu.Email;
            command.ExecuteNonQuery();
            con.Close();
            return 1;
        }
        catch
        {
            con.Close();
            return 0;
        }
    }

    
}